<?php
if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class DeadlineModel extends BaseModel {

	public function DeadlineModel() {
		parent::BaseModel();
		$this->setModel('deadline');
		$this->load->database();
	}
	
	public function getDeadlinesFor($jobid = 0) {
		$sql = "SELECT D.*, U.firstname, U.lastname
				FROM deadline D LEFT JOIN users U ON (D.deadline_ownerid = U.id)
				WHERE D.jobid = ".intval($jobid)." 
					AND D.deleted = 0 
					AND completed = 0 ";
		
		$sql .=	" ORDER BY D.deadline_duedate, D.deadline_title DESC";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getMissedDeadlinesFor($jobid = 0, $userid = 0) {
		$sql = "SELECT D.*, U.firstname, U.lastname, J.job_title, C.id as companyid, C.company_name
				FROM deadline D LEFT JOIN users U ON (D.deadline_ownerid = U.id)
					LEFT JOIN job J ON (D.jobid = J.id)
					LEFT JOIN company C ON (J.companyid = C.id)
				WHERE D.deleted = 0 
					AND completed = 0 
					AND deadline_duedate < NOW() ";
		
		if ($jobid) $sql .= " AND D.jobid = ".intval($jobid)." ";
		if ($userid) $sql .= " AND (D.deadline_ownerid = ".intval($userid)." OR C.account_ownerid = ".intval($userid).") ";
		
		$sql .=	" ORDER BY D.deadline_duedate, D.deadline_title DESC";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getNextXDaysDeadlinesForUser($startdate = 'now()', $days = 7, $userid = 0) {
		$sql = "SELECT D.* , X.job_title, X.companyid, X.company_name, X.job_costcenterid, X.centertitle, X.activityid, X.activity_title
				FROM (SELECT C.id as companyid, A.*, C.company_name, J.job_title, C2.centertitle, A2.activity_title
						FROM assignment A LEFT JOIN job J ON (A.jobid = J.id)
							LEFT JOIN company C ON (J.companyid = C.id)
							LEFT JOIN job_costcenter JC ON (A.job_costcenterid = JC.id)
							LEFT JOIN costcenter C2 ON (JC.costcenterid = C2.id)
							LEFT JOIN activity A2 ON (A.activityid = A2.id)
						WHERE A.userid = ".intval($userid)."
							AND J.archived = 0
							AND (J.job_status = 'live' OR J.job_status = 'proposed')
						GROUP BY J.id) X LEFT JOIN deadline D ON (X.jobid = D.jobid)
				WHERE D.deleted = 0 AND DateDiff(D.deadline_duedate, now() ) <= ".intval($days) ." ";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
		
	}
	
}

?>